import time
import json
import csv
from datetime import datetime
from sp_api.api import Catalog, Products
from sp_api.base import Marketplaces
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.intereface_amazon.databasemodel import AmazonProduct, Base

class AmazonBatchOperations:
    def __init__(self, marketplace_id='ATVPDKIKX0DER', db_url=None):
        """
        初始化亚马逊批量操作工具
        
        参数:
            marketplace_id: 市场ID，默认为美国市场
            db_url: 数据库连接URL
        """
        self.marketplace_id = marketplace_id
        self.marketplace = Marketplaces.US  # 默认使用美国市场
        
        # 初始化API客户端
        self.catalog_api = Catalog(marketplace=self.marketplace)
        self.products_api = Products(marketplace=self.marketplace)
        
        # 初始化数据库连接
        if db_url:
            self.engine = create_engine(db_url)
            Base.metadata.create_all(self.engine)
            self.Session = sessionmaker(bind=self.engine)
    
    def download_products(self, asin_list, batch_size=20):
        """
        批量下载亚马逊产品信息
        
        参数:
            asin_list: ASIN列表
            batch_size: 每批处理的ASIN数量，默认为20
        
        返回:
            下载结果列表
        """
        download_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(asin_list), batch_size):
                batch = asin_list[i:i+batch_size]
                
                try:
                    # 调用Catalog API获取产品信息
                    response = self.catalog_api.get_catalog_items(
                        asins=batch,
                        includedData=['attributes', 'identifiers', 'images', 'productTypes', 'salesRanks', 'summaries']
                    )
                    
                    # 处理响应
                    if response.payload and 'items' in response.payload:
                        for item in response.payload['items']:
                            try:
                                # 提取产品信息
                                product_data = self._extract_product_data(item)
                                
                                # 检查产品是否已存在
                                existing_product = session.query(AmazonProduct).filter_by(asin=product_data['asin']).first()
                                
                                if existing_product:
                                    # 更新现有产品
                                    for key, value in product_data.items():
                                        setattr(existing_product, key, value)
                                    existing_product.updated_at = datetime.now()
                                else:
                                    # 创建新产品
                                    new_product = AmazonProduct(**product_data)
                                    session.add(new_product)
                                
                                download_results.append({
                                    'asin': product_data['asin'],
                                    'status': 'success'
                                })
                                
                            except Exception as e:
                                download_results.append({
                                    'asin': item.get('asin', 'unknown'),
                                    'status': 'error',
                                    'message': str(e)
                                })
                                print(f"处理产品时出错: {e}")
                                continue
                    
                    # 提交事务
                    session.commit()
                    
                    # 添加延迟以避免API限制
                    time.sleep(1)
                    
                except Exception as e:
                    session.rollback()
                    download_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"处理ASIN批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return download_results
    
    def upload_products(self, product_data_list, batch_size=5):
        """
        批量上传产品到亚马逊
        
        参数:
            product_data_list: 产品数据列表，每个元素是一个字典
            batch_size: 每批处理的产品数量，默认为5
        
        返回:
            上传结果列表
        """
        upload_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(product_data_list), batch_size):
                batch = product_data_list[i:i+batch_size]
                
                try:
                    # 准备产品数据
                    products = []
                    for product in batch:
                        # 转换产品数据为API格式
                        api_product = self._convert_to_api_format(product)
                        products.append(api_product)
                    
                    # 调用Products API上传产品
                    response = self.products_api.create_product(
                        body={
                            'products': products
                        }
                    )
                    
                    # 处理响应
                    if response.payload:
                        # 保存到数据库
                        for product in batch:
                            try:
                                # 检查产品是否已存在
                                existing_product = session.query(AmazonProduct).filter_by(sku=product['sku']).first()
                                
                                if existing_product:
                                    # 更新现有产品
                                    for key, value in product.items():
                                        setattr(existing_product, key, value)
                                    existing_product.updated_at = datetime.now()
                                else:
                                    # 创建新产品
                                    new_product = AmazonProduct(**product)
                                    session.add(new_product)
                                
                                upload_results.append({
                                    'sku': product['sku'],
                                    'status': 'success'
                                })
                                
                            except Exception as e:
                                upload_results.append({
                                    'sku': product.get('sku', 'unknown'),
                                    'status': 'error',
                                    'message': str(e)
                                })
                                print(f"保存产品到数据库时出错: {e}")
                                continue
                        
                        # 提交事务
                        session.commit()
                    else:
                        upload_results.append({
                            'batch': i // batch_size + 1,
                            'status': 'error',
                            'message': 'No payload in response'
                        })
                    
                    # 添加延迟以避免API限制
                    time.sleep(2)
                    
                except Exception as e:
                    session.rollback()
                    upload_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"上传产品批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return upload_results
    
    def update_products(self, product_updates, batch_size=5):
        """
        批量修改亚马逊产品
        
        参数:
            product_updates: 产品更新数据列表，每个元素是一个字典
            batch_size: 每批处理的产品数量，默认为5
        
        返回:
            更新结果列表
        """
        update_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(product_updates), batch_size):
                batch = product_updates[i:i+batch_size]
                
                try:
                    # 准备产品更新数据
                    updates = []
                    for update in batch:
                        # 转换产品数据为API格式
                        api_update = self._convert_to_api_format(update)
                        updates.append(api_update)
                    
                    # 调用Products API更新产品
                    response = self.products_api.patch_product(
                        body={
                            'products': updates
                        }
                    )
                    
                    # 处理响应
                    if response.payload:
                        # 更新数据库
                        for update in batch:
                            try:
                                # 查找产品
                                product = session.query(AmazonProduct).filter_by(sku=update['sku']).first()
                                
                                if product:
                                    # 更新产品
                                    for key, value in update.items():
                                        if key != 'sku':  # 不更新SKU
                                            setattr(product, key, value)
                                    product.updated_at = datetime.now()
                                
                                update_results.append({
                                    'sku': update['sku'],
                                    'status': 'success'
                                })
                                
                            except Exception as e:
                                update_results.append({
                                    'sku': update.get('sku', 'unknown'),
                                    'status': 'error',
                                    'message': str(e)
                                })
                                print(f"更新产品到数据库时出错: {e}")
                                continue
                        
                        # 提交事务
                        session.commit()
                    else:
                        update_results.append({
                            'batch': i // batch_size + 1,
                            'status': 'error',
                            'message': 'No payload in response'
                        })
                    
                    # 添加延迟以避免API限制
                    time.sleep(2)
                    
                except Exception as e:
                    session.rollback()
                    update_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"更新产品批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return update_results
    
    def delete_products(self, sku_list, batch_size=5, update_db=True):
        """
        批量删除亚马逊产品
        
        参数:
            sku_list: SKU列表
            batch_size: 每批处理的SKU数量，默认为5
            update_db: 是否更新数据库，默认为True
        
        返回:
            删除结果列表
        """
        delete_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(sku_list), batch_size):
                batch = sku_list[i:i+batch_size]
                
                try:
                    # 准备删除请求
                    delete_request = {
                        'skus': batch
                    }
                    
                    # 调用Products API删除产品
                    response = self.products_api.delete_product(
                        body=delete_request
                    )
                    
                    # 处理响应
                    if response.payload:
                        # 更新数据库
                        if update_db:
                            for sku in batch:
                                try:
                                    # 查找产品
                                    product = session.query(AmazonProduct).filter_by(sku=sku).first()
                                    
                                    if product:
                                        # 更新产品状态为已删除
                                        product.status = 'deleted'
                                        product.updated_at = datetime.now()
                                    
                                    delete_results.append({
                                        'sku': sku,
                                        'status': 'success'
                                    })
                                    
                                except Exception as e:
                                    delete_results.append({
                                        'sku': sku,
                                        'status': 'error',
                                        'message': str(e)
                                    })
                                    print(f"更新产品状态到数据库时出错: {e}")
                                    continue
                            
                            # 提交事务
                            session.commit()
                        else:
                            for sku in batch:
                                delete_results.append({
                                    'sku': sku,
                                    'status': 'success'
                                })
                    else:
                        delete_results.append({
                            'batch': i // batch_size + 1,
                            'status': 'error',
                            'message': 'No payload in response'
                        })
                    
                    # 添加延迟以避免API限制
                    time.sleep(2)
                    
                except Exception as e:
                    session.rollback()
                    delete_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"删除产品批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return delete_results
    
    def _extract_product_data(self, item):
        """
        从API响应中提取产品数据
        
        参数:
            item: API响应中的产品项
        
        返回:
            产品数据字典
        """
        asin = item.get('asin')
        attributes = item.get('attributes', {})
        identifiers = item.get('identifiers', {})
        images = item.get('images', [])
        product_types = item.get('productTypes', [])
        sales_ranks = item.get('salesRanks', [])
        summaries = item.get('summaries', [])
        
        # 提取主图URL
        main_image_url = None
        if images and len(images) > 0:
            main_image_url = images[0]['link']
        
        # 构建产品数据字典
        product_data = {
            'asin': asin,
            'attributes': attributes,
            'identifiers': identifiers,
            'images': images,
            'productTypes': product_types,
            'salesRanks': sales_ranks,
            'summaries': summaries,
            'main_image_url': main_image_url
        }
        
        return product_data
    
    def _convert_to_api_format(self, product):
        """
        将产品数据转换为API格式
        
        参数:
            product: 产品数据字典
        
        返回:
            API格式的产品数据
        """
        # 提取属性
        attributes = {}
        
        # 基本属性
        if 'title' in product:
            attributes['title'] = product['title']
        if 'brand' in product:
            attributes['brand'] = product['brand']
        if 'description' in product:
            attributes['description'] = product['description']
        if 'item_type' in product:
            attributes['item_type'] = product['item_type']
        if 'manufacturer' in product:
            attributes['manufacturer'] = product['manufacturer']
        if 'part_number' in product:
            attributes['part_number'] = product['part_number']
        if 'product_tax_code' in product:
            attributes['product_tax_code'] = product['product_tax_code']
        
        # 价格
        if 'standard_price' in product and 'currency' in product:
            attributes['standard_price'] = {
                'amount': float(product['standard_price']),
                'currency': product['currency']
            }
        
        # 要点
        if 'bullet_points' in product:
            if isinstance(product['bullet_points'], str):
                try:
                    bullet_points = json.loads(product['bullet_points'])
                except:
                    bullet_points = [product['bullet_points']]
            else:
                bullet_points = product['bullet_points']
            attributes['bullet_point'] = bullet_points
        
        # 构建API格式的产品数据
        api_product = {
            'sku': product['sku'],
            'productType': product.get('product_type', 'PRODUCT'),
            'requirements': 'LISTING',
            'attributes': attributes
        }
        
        return api_product 